 /**
   * Copyright 2014 Viettel Telecom. All rights reserved.
   * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
   */
 package com.viettel.core.dao.cms; 
import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.CmsUser;
import com.viettel.core.entities.vo.AssignExpertFilter;
import com.viettel.core.entities.vo.AssignExpertVO;
import com.viettel.core.entities.vo.CMSUserSearchFilter;
import com.viettel.core.entities.vo.CMSUserSearchVO;
import com.viettel.core.enumtype.CmsUserType;
import com.viettel.core.enumtype.UserStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;
 /**
   *  Mo ta muc dich cua lop (interface)
   *  @author: TOANTM1
   *  @version:  
   *  @since: 27/02/2014 - 10:50:59
   */
 public class CmsUserDAOImpl implements CmsUserDAO { 
	 private IRepository repo; 
		
	 	public void setRepo(IRepository repo) {
			this.repo = repo;
		} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public CmsUser getCmsUserById(Integer id) throws DataAccessException { 
		return repo.getEntityById(CmsUser.class, id); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public CmsUser createCmsUser(CmsUser cmsuser) throws DataAccessException { 
		if (cmsuser == null) { 
			throw new IllegalArgumentException("cmsuser"); 
		} 
		repo.create(cmsuser); 
		return repo.getEntityById(CmsUser.class, cmsuser.getId()); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void updateCmsUser(CmsUser cmsuser) throws DataAccessException { 
		if (cmsuser == null) { 
			throw new IllegalArgumentException("cmsuser"); 
		} 
		repo.update(cmsuser); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 10:50:59
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void deleteCmsUser(CmsUser cmsuser) throws DataAccessException { 
		if (cmsuser == null) { 
			throw new IllegalArgumentException("cmsuser"); 
		} 
		repo.delete(cmsuser); 
	}

	@Override
	public CmsUser getCmsUserByUserName(String userName)
			throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from CMS_USER cs where lower(cs.username) = lower(?) and status != -1 ");
		List<Object> params = new ArrayList<Object>();
		params.add(StringUtil.removeSpecialChars(userName));
		return repo.getEntityBySQL(CmsUser.class, sql.toString(), params);
	}

	@Override
	public List<CMSUserSearchVO> searchCMSUser(CMSUserSearchFilter filter)
			throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();

		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
			
		selectSql.append("SELECT us.id              AS id, ");
		selectSql.append("       us.username        AS userName, ");
		selectSql.append("       us.name            AS name, ");
		selectSql.append("       us.phone           AS phone, ");
		selectSql.append("       us.email           AS email, ");
		selectSql.append("       us.type            AS type, ");
		selectSql.append("       us.description     AS description, ");
		selectSql.append("       us.status          AS status, ");
		selectSql.append("       us.media_id        AS mediaId, ");
		selectSql.append("       me.name        	AS mediaName ");
		
		fromQuery.append("FROM cms_user us ");
		fromQuery.append("	LEFT JOIN Media me ");
		fromQuery.append("	ON us.media_id = me.id ");
		
		fromQuery.append("WHERE us.status != ? ");
		paramSelect.add(UserStatus.DELETED.getValue());
		countParams.add(UserStatus.DELETED.getValue());
		
		if (filter.getId() != null ) {
			fromQuery.append(" AND  us.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (filter.getType() != null ) {
			fromQuery.append(" AND  us.type = ? ");
			paramSelect.add(filter.getType());
			countParams.add(filter.getType());
		}
		
		if (filter.getMediaId() != null ) {
			fromQuery.append(" AND  us.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getName())) {
			fromQuery.append(" AND Upper(us.name_text) LIKE Upper(?) ESCAPE '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getUserName())) {
			fromQuery.append(" AND  Upper(us.username) LIKE Upper(?) ESCAPE '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getUserName()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getUserName()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getPhone())) {
			fromQuery.append(" AND  us.phone LIKE ? '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getPhone()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getPhone()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getEmail())) {
			fromQuery.append(" AND  Upper(us.email) LIKE Upper(?) ESCAPE '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getEmail()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getEmail()) + "%");
		}

		if(filter.getFromDate() != null && filter.getToDate() != null ){
			fromQuery.append("  AND TRUNC(us.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) ");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
			
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
		
		selectSql.append(fromQuery).append(" ORDER BY us.created_time DESC ");
		countQuery.append("SELECT COUNT(*) AS count ").append(fromQuery);
		String[] fieldNames = new String[] { 
				"id", "userName", "name", 
				"phone","email", "type",
				"description" ,"status", "mediaId", "mediaName"};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.INTEGER, 
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		
		List<CMSUserSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					CMSUserSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(CMSUserSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}
		return list;
	}

	@Override
	public List<CMSUserSearchVO> searchCMSUserByType(CMSUserSearchFilter filter)
			throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();

		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
			
		selectSql.append("SELECT us.id              AS id, ");
		selectSql.append("       us.username        AS userName, ");
		selectSql.append("       us.name            AS name, ");
		selectSql.append("       us.phone           AS phone, ");
		selectSql.append("       us.email           AS email, ");
		selectSql.append("       us.type            AS type, ");
		selectSql.append("       us.description     AS description, ");
		selectSql.append("       us.status          AS status, ");
		selectSql.append("       us.media_id        AS mediaId, ");
		selectSql.append("       me.name        	AS mediaName ");
		
		fromQuery.append("FROM cms_user us ");
		fromQuery.append("	LEFT JOIN Media me ");
		fromQuery.append("	ON us.media_id = me.id ");
		
		fromQuery.append("WHERE us.status != ? ");
		paramSelect.add(UserStatus.DELETED.getValue());
		countParams.add(UserStatus.DELETED.getValue());
		
		if (filter.getId() != null ) {
			fromQuery.append(" AND  us.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (filter.getType() != null ) {
			fromQuery.append(" AND  us.type = ? ");
			paramSelect.add(filter.getType());
			countParams.add(filter.getType());
		}
		
		if (filter.getMediaId() != null ) {
			fromQuery.append(" AND  us.media_id = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getName())) {
			fromQuery.append(" AND Upper(us.name_text) LIKE Upper(?) ESCAPE '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getUserName())) {
			fromQuery.append(" AND  Upper(us.username) LIKE Upper(?) ESCAPE '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getUserName()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getUserName()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getPhone())) {
			fromQuery.append(" AND  us.phone LIKE ? '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getPhone()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getPhone()) + "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getEmail())) {
			fromQuery.append(" AND  Upper(us.email) LIKE Upper(?) ESCAPE '/' ");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getEmail()) + "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getEmail()) + "%");
		}

		if(filter.getFromDate() != null && filter.getToDate() != null ){
			fromQuery.append("  AND TRUNC(us.CREATED_TIME) BETWEEN TRUNC(?) AND TRUNC(?) ");
			paramSelect.add(filter.getFromDate());
			countParams.add(filter.getFromDate());
			
			paramSelect.add(filter.getToDate());
			countParams.add(filter.getToDate());
		}
		
		selectSql.append(fromQuery).append(" ORDER BY LOWER(us.name) ");
		countQuery.append("SELECT COUNT(*) AS count ").append(fromQuery);
		String[] fieldNames = new String[] { 
				"id", "userName", "name", 
				"phone","email", "type",
				"description" ,"status", "mediaId", "mediaName"};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.STRING, StandardBasicTypes.STRING, StandardBasicTypes.INTEGER, 
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,
				StandardBasicTypes.STRING};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		
		List<CMSUserSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					CMSUserSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(CMSUserSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}
		return list;
	}
	
	@Override
	public Boolean checkUserNameExist(String userName) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select count(1) count from dual where exists (select 1 from cms_user where Upper(username) = ? and status != ?)");
		params.add(userName.toUpperCase());
		params.add(UserStatus.DELETED.getValue());
		int c = repo.countBySQL(sql.toString(), params);
		return c > 0 ? true : false;
	} 
	
	@Override
	public List<AssignExpertVO> searchExpertByFilter(AssignExpertFilter filter)
			throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();

		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
			
		selectSql.append("SELECT temp.id as expertId, ");
		selectSql.append("      (select count(1) from ASSIGN_QUESTION AS1 ");
		selectSql.append("      JOIN QUESTIONS Q1 ON Q1.ID = AS1.QUESTION_ID AND Q1.STATUS = 2  ");
		selectSql.append("      where AS1.ASSIGN_TO=ASS.ASSIGN_TO ) as numQuestion ");
		
				
		fromQuery.append(" FROM (SELECT US.ID ");
		fromQuery.append("		FROM CMS_USER US JOIN EXPERT_GROUP EG ");
		fromQuery.append("		ON US.ID = EG.cms_users_id ");
		fromQuery.append("		JOIN EXPERT_TECHNIQUE ET ");
		fromQuery.append("		ON US.ID = ET.cms_users_id ");
		fromQuery.append("		WHERE US.STATUS != ? AND US.TYPE = ? ");
		
		paramSelect.add(UserStatus.DELETED.getValue());
		countParams.add(UserStatus.DELETED.getValue());
		paramSelect.add(CmsUserType.CHUYENGIA.getValue());
		countParams.add(CmsUserType.CHUYENGIA.getValue());
		
		if (filter.getGroupCategoryId() != null ) {
			fromQuery.append(" AND  EG.GROUP_CATEGORY_ID = ? ");
			paramSelect.add(filter.getGroupCategoryId());
			countParams.add(filter.getGroupCategoryId());
		}
		
		if (filter.getTechniqueCategoryId() != null ) {
			fromQuery.append(" AND  ET.TECHNIQUE_CATEGORY_ID = ? ");
			paramSelect.add(filter.getTechniqueCategoryId());
			countParams.add(filter.getTechniqueCategoryId());
		}
		
		if (filter.getMediaId() != null ) {
			fromQuery.append(" AND  US.MEDIA_ID = ? ");
			paramSelect.add(filter.getMediaId());
			countParams.add(filter.getMediaId());
		}
		
		fromQuery.append("		GROUP BY US.ID) TEMP");
		fromQuery.append(" LEFT JOIN ASSIGN_QUESTION ASS  ");
		fromQuery.append(" ON ASS.ASSIGN_TO = TEMP.ID ");
		//fromQuery.append(" WHERE ASS.COUNT <= 2 ");
		
		selectSql.append(fromQuery).append(" ORDER BY numQuestion ASC ");
		
		countQuery.append("SELECT COUNT(*) AS count ").append(fromQuery);
		String[] fieldNames = new String[] { 
				"expertId", "numQuestion"};
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER };

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		
		List<AssignExpertVO> list = null;
		
		list = repo.getListByQueryAndScalar(AssignExpertVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		
		return list;
	}
	
} 
 
